Inserting Records Using INSERT INTO in MySQL – Complete Detailed Notes

The INSERT INTO statement is one of the most important SQL commands used to add new records to MySQL tables. It allows developers to insert a single record, multiple records, selected column values, default values, or data retrieved from another table. Understanding INSERT INTO is essential for database development, application programming, and data management.

MySQL Training Course: JustAcademy MySQL Training Course

Course Demo: Register for MySQL Course Demo

1. Introduction to INSERT INTO

In MySQL, a table stores information in rows and columns. The INSERT INTO statement adds new rows to an existing table. For example, a college database can use INSERT INTO to add student details, a training institute can store course information, and an e-commerce application can record new products.

INSERT is a Data Manipulation Language (DML) statement because it is used to manipulate the data stored in database tables.

Common applications include:

• Adding new student records to a college database.
• Registering new employees in an employee management system.
• Saving product information in an inventory database.
• Recording customer details in a business application.
• Storing orders, payments, and enrollment information.
• Copying selected records from one table to another.

2. Learning Objectives

After completing these notes, you will be able to:

• Understand the purpose and syntax of INSERT INTO.
• Insert a single record into a MySQL table.
• Insert records into selected columns.
• Insert multiple records in one statement.
• Use DEFAULT and NULL values correctly.
• Insert data into tables containing AUTO_INCREMENT columns.
• Use INSERT INTO ... SET syntax.
• Insert records using SELECT queries.
• Understand common insertion errors and their solutions.
• Apply INSERT INTO in a practical database project.

3. Basic Syntax of INSERT INTO

The INSERT INTO statement can be used with a list of column names and corresponding values.

Syntax

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

Explanation:

• INSERT INTO specifies that new data will be inserted.
• table_name is the name of the table receiving the records.
• column1, column2, column3 specify the columns to populate.
• VALUES introduces the values for the new row.
• value1, value2, value3 are the values corresponding to the listed columns.
• The semicolon marks the end of the SQL statement.

The number of supplied values must match the number of listed columns, and the values must be compatible with their respective column definitions.

4. Preparing a Database for INSERT INTO

Before inserting records, you need an existing database and table.

Step 1: Create a Database

CREATE DATABASE college_db;

Step 2: Select the Database

USE college_db;

Step 3: Create a Students Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    course_name VARCHAR(100) DEFAULT 'MySQL',
    admission_date DATE
);

This table contains six columns:

5. Inserting a Single Record

The simplest use of INSERT INTO is adding one record to a table.

Example

INSERT INTO students
    (student_name, email, age, course_name, admission_date)
VALUES
    ('Aarav Sharma', '[email protected]', 22, 'MySQL', '2026-10-01');

This statement inserts one student record. MySQL automatically generates the student_id because the column is defined with AUTO_INCREMENT.

Verify the Inserted Record

SELECT * FROM students;

The SELECT statement displays the stored records. The generated student ID depends on the table's current AUTO_INCREMENT value and is not necessarily always 1.

6. Inserting Records Without Specifying All Columns

You do not have to provide values for every column if omitted columns can legally use a default value, generate a value automatically, or accept NULL.

Example

INSERT INTO students (student_name, email)
VALUES ('Priya Verma', '[email protected]');

In this example:

• student_id is generated automatically.
• student_name is set to Priya Verma.
• email is set to [email protected].
• age is omitted and can be NULL.
• course_name uses its default value, MySQL.
• admission_date is omitted and can be NULL.

Omitting a column is different from explicitly inserting NULL. When a column is omitted, MySQL uses its default behavior. An explicit NULL is stored as NULL if allowed; otherwise, the insertion may fail or be adjusted depending on the column definition and SQL mode.

7. Inserting Records into Specific Columns

When a table contains many columns, you can insert values into only the columns required for a particular record.

Syntax

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Example

INSERT INTO students (student_name, age)
VALUES ('Rahul Mehta', 24);

This inserts the student's name and age while allowing MySQL to handle the remaining columns according to their defaults, nullability, and automatic-generation rules.

Best practice: Specify the column names explicitly. This makes SQL easier to understand and helps prevent errors when the table structure changes.

8. Inserting Multiple Records in One Statement

MySQL allows multiple rows to be inserted in a single INSERT INTO statement. This is convenient when importing sample data or adding several records at once.

Syntax

INSERT INTO table_name (column1, column2)
VALUES
    (value1, value2),
    (value3, value4),
    (value5, value6);

Example

INSERT INTO students
    (student_name, email, age, course_name, admission_date)
VALUES
    ('Neha Singh', '[email protected]', 21, 'MySQL', '2026-10-02'),
    ('Rohan Patel', '[email protected]', 23, 'Python', '2026-10-03'),
    ('Simran Kaur', '[email protected]', 22, 'Data Analytics', '2026-10-04');

This statement attempts to insert three student records in one operation.

Advantages of Multiple-Row INSERT

• Reduces the number of SQL statements needed.
• Can reduce communication overhead between an application and the database.
• Makes sample-data setup faster.
• Improves convenience when importing a small batch of records.

All rows must follow the same column list and provide a matching number of values. If a row violates a constraint, the result depends on the statement, storage engine, and error-handling options; without IGNORE, a constraint error generally causes the statement to fail.

9. Inserting Data into an AUTO_INCREMENT Column

AUTO_INCREMENT is commonly used for primary-key columns. MySQL generates a numeric value for the column when the INSERT statement omits it or supplies NULL in the supported usual cases.

Example: Let MySQL Generate the ID

INSERT INTO students
    (student_name, email, age)
VALUES
    ('Karan Joshi', '[email protected]', 25);

MySQL generates the value for student_id automatically.

Example: Explicitly Insert NULL into the AUTO_INCREMENT Column

INSERT INTO students
    (student_id, student_name, email)
VALUES
    (NULL, 'Ananya Rao', '[email protected]');

For a typical AUTO_INCREMENT column, inserting NULL requests a generated ID. The actual behavior can depend on the column definition and SQL mode.

Check the Generated ID

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() returns the first automatically generated value from the most recent relevant INSERT operation in the current connection. For a multi-row insert, it does not return every generated ID.

Important: AUTO_INCREMENT values are not guaranteed to be consecutive. Gaps can occur due to deleted rows, failed statements, or other database operations.

10. Inserting Default Values

The DEFAULT keyword tells MySQL to use the defined default value for a column.

Example

INSERT INTO students
    (student_name, email, course_name)
VALUES
    ('Ishita Das', '[email protected]', DEFAULT);

In this example, course_name receives the default value MySQL.

Insert a Row Using DEFAULT VALUES for Individual Columns

To use a default for a particular column while providing other values, specify DEFAULT in that column's position.

INSERT INTO students
    (student_name, email, age, course_name)
VALUES
    ('Dev Malhotra', '[email protected]', 20, DEFAULT);

The DEFAULT keyword is useful when you want to make the intended behavior explicit rather than relying on an omitted column.

11. Inserting NULL Values

NULL represents a missing, unknown, or unavailable value. It is not the same as zero, an empty string, or the text 'NULL'. A column must permit NULL for an explicit NULL value to be stored normally.

Example

INSERT INTO students
    (student_name, email, age, admission_date)
VALUES
    ('Meera Shah', '[email protected]', NULL, NULL);

This statement inserts a student whose age and admission date are unknown or not provided.

Important Rules

• Use the SQL keyword NULL without quotation marks.
• Use NOT NULL in a column definition when a value must be supplied.
• Use IS NULL to find NULL values in queries.
• Use IS NOT NULL to find non-NULL values.

Example: Find Students Without an Age

SELECT *
FROM students
WHERE age IS NULL;

12. Using INSERT INTO ... SET

MySQL supports an alternative INSERT syntax that assigns a value to each column using an equals sign. This can be convenient when inserting a single row.

Syntax

INSERT INTO table_name
SET column1 = value1,
    column2 = value2;

Example

INSERT INTO students
SET
    student_name = 'Arjun Nair',
    email = '[email protected]',
    age = 22,
    course_name = 'SQL';

This inserts one record into the students table. Columns not specified follow their default, NULL, or automatic-generation behavior.

Note: INSERT ... SET is a MySQL-specific form. For general SQL portability and multi-row inserts, the column-list and VALUES form is usually preferable.

13. Inserting Data Using INSERT INTO ... SELECT

The INSERT INTO ... SELECT statement copies data returned by a SELECT query into another table. It is useful for data migration, archiving, reporting, and transferring selected records between tables.

Example: Create an Archive Table

CREATE TABLE students_archive (
    student_id INT,
    student_name VARCHAR(100),
    email VARCHAR(150),
    age INT,
    course_name VARCHAR(100),
    admission_date DATE
);

Copy Records into the Archive Table

INSERT INTO students_archive
    (student_id, student_name, email, age, course_name, admission_date)
SELECT
    student_id, student_name, email, age, course_name, admission_date
FROM students
WHERE course_name = 'MySQL';

This copies students whose course name is MySQL into the archive table.

The number and order of the columns selected by the SELECT query must correspond to the destination columns specified in the INSERT statement. The values must also be compatible with the destination column definitions.

14. Inserting Records with Expressions and Functions

Values supplied to INSERT can include expressions and functions, not just literal values.

Example: Insert the Current Date

INSERT INTO students
    (student_name, email, admission_date)
VALUES
    ('Tanya Kapoor', '[email protected]', CURRENT_DATE());

CURRENT_DATE() returns the current date according to the database session's environment.

Example: Insert a Calculated Value

Suppose a products table stores product names and prices.

CREATE TABLE products (
    product_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);
INSERT INTO products (product_name, price)
VALUES ('Keyboard', 500.00 + 250.00);

The price expression evaluates to 750.00 before it is stored, subject to the column's data type and constraints.

15. Handling Duplicate Values

When a table has a PRIMARY KEY or UNIQUE constraint, MySQL prevents conflicting duplicate values unless a statement uses a specific conflict-handling approach.

Example: Table with a Unique Email

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE
);

Insert the first customer:

INSERT INTO customers (customer_name, email)
VALUES ('Amit Verma', '[email protected]');

Trying to insert another customer with the same non-NULL email normally results in a duplicate-key error:

INSERT INTO customers (customer_name, email)
VALUES ('Another Amit', '[email protected]');

To avoid the error, validate the input or choose an appropriate conflict-handling strategy. Do not remove a UNIQUE constraint merely to bypass an error unless duplicate values are genuinely allowed by the business requirements.

16. Using INSERT IGNORE

INSERT IGNORE tells MySQL to handle certain errors as warnings and may skip conflicting rows or adjust invalid values, depending on the error and SQL mode. It should not be used as a general replacement for data validation.

Example

INSERT IGNORE INTO customers
    (customer_name, email)
VALUES
    ('Amit Verma', '[email protected]'),
    ('New Customer', '[email protected]');

If one row conflicts with a UNIQUE or PRIMARY KEY constraint, MySQL may skip that conflicting row and continue processing other rows. Review warnings and the affected-row count to confirm what happened.

SHOW WARNINGS;

Important: INSERT IGNORE can also suppress or downgrade certain data errors. Use it only when that behavior is appropriate and understood.

17. Using ON DUPLICATE KEY UPDATE

MySQL supports ON DUPLICATE KEY UPDATE for situations where a new row conflicts with an existing PRIMARY KEY or UNIQUE index. Instead of simply returning a duplicate-key error, the statement updates the existing row according to the specified assignments.

Example

INSERT INTO customers (customer_name, email)
VALUES ('Amit Verma', '[email protected]')
ON DUPLICATE KEY UPDATE
    customer_name = 'Amit Verma';

If the inserted key conflicts with an existing primary or unique key, the matching row is updated as specified. If no conflict occurs, a new row is inserted.

This pattern is often used for upsert-style operations, but the update logic must be designed carefully to avoid unintentionally overwriting existing data.

18. Common INSERT INTO Errors and Solutions

19. Best Practices for INSERT INTO

• Specify column names explicitly rather than relying on the table's column order.
• Match each value to the correct column and data type.
• Use single quotation marks for text and date literals.
• Do not put quotation marks around SQL NULL or the DEFAULT keyword.
• Validate user input before inserting it into the database.
• Use parameterized queries or prepared statements in application code to reduce SQL injection risks.
• Respect PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and foreign-key constraints.
• Use transactions when a group of related inserts must succeed or fail together.
• Check affected-row counts and database warnings when appropriate.
• Use multi-row INSERT statements for suitable batch inserts.
• Use INSERT INTO ... SELECT when copying data directly from another table.
• Back up important data and test database changes in a safe environment.

20. Transactions and INSERT INTO

When using a transactional storage engine such as InnoDB, INSERT statements can be managed within transactions. A transaction allows related operations to be committed together or rolled back if a problem occurs before the commit.

Example

START TRANSACTION;
INSERT INTO students (student_name, email, age)
VALUES ('Nisha Jain', '[email protected]', 21);
INSERT INTO students (student_name, email, age)
VALUES ('Varun Sethi', '[email protected]', 23);
COMMIT;

The COMMIT statement makes the transaction's changes permanent. If an error occurs before committing and the transaction is still active, you can use ROLLBACK to discard its uncommitted changes.

START TRANSACTION;
INSERT INTO students (student_name, email)
VALUES ('Temporary Student', '[email protected]');
ROLLBACK;

After a successful ROLLBACK, the uncommitted insert is undone. Transaction behavior depends on the storage engine and execution context; AUTO_INCREMENT counters may still advance, so gaps in IDs can occur even when a transaction is rolled back.

21. Practical Project: Student Registration System

In this practical project, you will create a student table, insert records using different INSERT INTO methods, and verify the results.

Step 1: Create and Select a Database

CREATE DATABASE student_management;
USE student_management;

Step 2: Create the Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    course_name VARCHAR(100) DEFAULT 'MySQL',
    admission_date DATE
);

Step 3: Insert One Record

INSERT INTO students
    (student_name, email, age, course_name, admission_date)
VALUES
    ('Aarav Sharma', '[email protected]', 22, 'MySQL', '2026-10-01');

Step 4: Insert Multiple Records

INSERT INTO students
    (student_name, email, age, course_name, admission_date)
VALUES
    ('Priya Verma', '[email protected]', 21, 'Python', '2026-10-02'),
    ('Rahul Mehta', '[email protected]', 24, 'Data Analytics', '2026-10-03'),
    ('Simran Kaur', '[email protected]', 22, 'Web Development', '2026-10-04');

Step 5: Insert a Record Using a Default Value

INSERT INTO students
    (student_name, email, age, course_name)
VALUES
    ('Neha Singh', '[email protected]', 20, DEFAULT);

Step 6: Insert a Record Using SET

INSERT INTO students
SET
    student_name = 'Karan Joshi',
    email = '[email protected]',
    age = 25,
    course_name = 'SQL';

Step 7: Display All Records

SELECT * FROM students;

Step 8: Display Selected Columns

SELECT student_id, student_name, course_name
FROM students;

Step 9: Count the Records

SELECT COUNT(*) AS total_students
FROM students;

Step 10: Verify the Table Structure

DESCRIBE students;

Project outcome: You will be able to create a table, insert one or multiple records, use default values, retrieve inserted data, count records, and verify the table structure.

22. Frequently Asked Questions (FAQs)

Q1. What is INSERT INTO in MySQL?

INSERT INTO is a DML statement used to add new rows to an existing table.

Q2. Can I insert multiple records with one INSERT statement?

Yes. You can provide multiple comma-separated value lists in one INSERT INTO ... VALUES statement.

Q3. Do I have to provide values for every column?

No. You can omit columns when they have a default, allow NULL, or are automatically generated. Required columns without defaults must receive valid values.

Q4. How does AUTO_INCREMENT work during insertion?

MySQL generates a numeric value for an AUTO_INCREMENT column when the INSERT statement omits that column or uses a supported automatic-generation value such as NULL.

Q5. What is the difference between NULL and DEFAULT?

NULL represents a missing or unknown value. DEFAULT asks MySQL to use the column's defined default value.

Q6. What does INSERT INTO ... SELECT do?

It inserts rows returned by a SELECT query into a destination table.

Q7. What happens if I insert a duplicate unique value?

MySQL normally returns a duplicate-key error unless you use an appropriate conflict-handling method, such as ON DUPLICATE KEY UPDATE or INSERT IGNORE.

Q8. Is INSERT INTO a DDL or DML statement?

INSERT INTO is a Data Manipulation Language (DML) statement because it changes the data stored in a table rather than defining the table structure.

Q9. How can I verify that a record was inserted?

Use a SELECT query, such as SELECT * FROM students;, or query the specific record using its primary key or another suitable condition.

Q10. Can an INSERT statement be rolled back?

With a transactional storage engine such as InnoDB, an uncommitted insert can generally be rolled back within an active transaction. Once committed, it is no longer undone by a normal ROLLBACK.

23. Practice Questions

1. What is the purpose of the INSERT INTO statement?
2. Write the syntax for inserting one student record into a students table.
3. How can you insert multiple records in a single SQL statement?
4. Why is it recommended to specify column names in an INSERT statement?
5. What happens when a column with a DEFAULT value is omitted?
6. What is the difference between inserting NULL and using DEFAULT?
7. How do you insert data into a table with an AUTO_INCREMENT primary key?
8. Explain the INSERT INTO ... SET syntax with an example.
9. What is the purpose of INSERT INTO ... SELECT?
10. How can duplicate-key conflicts be handled in MySQL?
11. What is the difference between INSERT IGNORE and ON DUPLICATE KEY UPDATE?
12. How can you roll back an uncommitted insert in an InnoDB transaction?

24. Quick Revision

• INSERT INTO table_name (columns) VALUES (values); inserts one row.
• Multiple value lists can insert several rows in one statement.
• Explicit column lists make INSERT statements clearer and safer.
• Omitted columns use their applicable defaults, NULL behavior, or automatic-generation rules.
• DEFAULT requests the column's default value.
• NULL represents a missing or unknown value when permitted.
• INSERT INTO ... SET is an alternative MySQL syntax for inserting a row.
• INSERT INTO ... SELECT copies query results into a table.
• INSERT IGNORE can suppress or downgrade certain errors and should be used carefully.
• ON DUPLICATE KEY UPDATE handles primary-key or unique-key conflicts by updating an existing row.
• Use SELECT to verify the inserted data.
• Use transactions when related insert operations need to be committed or rolled back together.

25. Official Documentation and Further Learning

• MySQL Official Documentation: INSERT Statement
• MySQL Official Documentation: INSERT ... SELECT Statement
• MySQL Official Documentation: INSERT ... ON DUPLICATE KEY UPDATE
• MySQL Official Documentation: START TRANSACTION, COMMIT, and ROLLBACK

Conclusion

The INSERT INTO statement is essential for adding new data to MySQL databases. It supports single-row insertion, multiple-row insertion, selected columns, default values, automatic IDs, and data copied from SELECT queries. By understanding column definitions, validating values, handling constraints correctly, and verifying inserted records, you can maintain accurate and reliable databases.

Continue learning with the JustAcademy MySQL Training Course or register for a MySQL course demo.